/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.WS;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import javax.jws.WebMethod;
import javax.jws.WebParam;
import javax.jws.WebService;
import oracle.jdbc.driver.OracleDriver;

/**
 *
 * @author Trabajo
 */
@WebService()
public class ServicioInformacionGeneral
{

    /**
     * Web service operation
     */
    @WebMethod(operationName = "validarUsuario")
    public String validarUsuario(@WebParam(name = "login")
    final String login, @WebParam(name = "password")
    final String password)
    {
        try
        {
            DriverManager.registerDriver(new OracleDriver());
            Connection conexion =  DriverManager.getConnection("jdbc:oracle:thin:@chie.uniandes.edu.co:1522:chie10", "s3702a09", "a3702a09");
            Statement s = conexion.createStatement();
//                PreparedStatement statement =  conexion.prepareStatement("select usuarios.pass from usuarios where login = ?");
//                statement.setString(1, login);
            String string = "select usuarios.pass from usuarios where login = '" + login +"'";
                ResultSet set = s.executeQuery(string);
                String pass = "";
                if(set.next()){
                    pass = set.getString(1);
                }
                else{
                    set.close();
                    s.close();
                    conexion.close();
                    return "NOEXITO";
                }
                String resultado = "";
                if(pass.equalsIgnoreCase(password))
                    resultado = "EXITO";
                else
                    resultado = "NOEXITO";
                s.close();
                conexion.close();
                return resultado;
        }
        catch (SQLException ex)
        {
            return ex.getMessage();
        }
    }

    /**
     * Web service operation
     */
    @WebMethod(operationName = "listarTiposInseguridad")
    public Vector<String> listarTiposInseguridad()
    {
        try
        {
            DriverManager.registerDriver(new OracleDriver());
            Connection conexion =  DriverManager.getConnection("jdbc:oracle:thin:@chie.uniandes.edu.co:1522:chie10", "s3702a09", "a3702a09");
            Statement statement = conexion.createStatement();
            ResultSet set = statement.executeQuery("select * from tiposinseguridad");
            Vector<String> resultado = new Vector<String>();
            while(set.next())
            {
                resultado.add(set.getDouble(1) + "-" + set.getString(2));
            }
            set.close();
            statement.close();
            conexion.close();
            return resultado;
        }
        catch (SQLException ex)
        {
            return null;
        }
    }

    /**
     * Web service operation
     */
    @WebMethod(operationName = "ingresarLugar")
    public String ingresarLugar(@WebParam(name = "latitud")
    final float latitud, @WebParam(name = "longitud")
    final float longitud, @WebParam(name = "nombreLugar")
    final String nombreLugar, @WebParam(name = "loginUsuario")
    final String loginUsuario) {
         try
        {
            DriverManager.registerDriver(new OracleDriver());
            Connection conexion =  DriverManager.getConnection("jdbc:oracle:thin:@chie.uniandes.edu.co:1522:chie10", "s3702a09", "a3702a09");
            PreparedStatement statement = conexion.prepareStatement("insert into lugaresusuario values(seclugaresusuarios.nextval, ?, ?,?,(select usuarios.idusuario from usuarios where login = ?))");
            statement.setFloat(1, latitud);
            statement.setFloat(2, longitud);
            statement.setString(3, nombreLugar);
            statement.setString(4, loginUsuario);
            statement.execute();
            statement.close();
            conexion.close();
            return "EXITO";
        }
        catch (SQLException ex)
        {
            return "Excepcion:" + ex.getMessage();
        }
    }

    /**
     * Web service operation
     */
    @WebMethod(operationName = "listarLugaresUsuario")
    public Vector<String> listarLugaresUsuario(@WebParam(name = "login")
    final String login) {
        try
        {
            DriverManager.registerDriver(new OracleDriver());
            Connection conexion =  DriverManager.getConnection("jdbc:oracle:thin:@chie.uniandes.edu.co:1522:chie10", "s3702a09", "a3702a09");
            PreparedStatement statement = conexion.prepareStatement("select lugaresusuario.* from usuarios inner join lugaresusuario on usuarios.idusuario = lugaresusuario.idusuario where usuarios.login = ?");
            statement.setString(1, login);
            ResultSet set = statement.executeQuery();
            Vector<String> resultado = new Vector<String>();
            while(set.next())
            {
                resultado.add(set.getDouble(1) + "/" + set.getFloat(2)+ "/" + set.getFloat(3)+ "/" + set.getString(4));
            }
            set.close();
            statement.close();
            conexion.close();
            return resultado;
        }
        catch (SQLException ex)
        {
            return null;
        }
    }

    
}
